# 配置从ODS层到DWD层的脚本

# 需求：能够手动传入参数指定采集的日期。  如果不传入参数要求能够自动的识别前一天的日期
if [ $# -eq 0 ]
then
    DATESTR=`date -d "-1 day" +%Y-%m-%d`
else
    DATESTR=$1
fi

HIVE_HOME="/usr/bin/hive"

echo "即将执行：${DATESTR} 这一天的ODS -> DWD的过程......"

$HIVE_HOME -e "
-- 动态分区配置
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
-- hive压缩
set hive.exec.compress.intermediate=true;
set hive.exec.compress.output=true;
-- 写入时ORC压缩生效
set hive.exec.orc.compression.strategy=COMPRESSION;
INSERT INTO TABLE itcast_dwd.visit_consult_dwd partition(yearinfo, quarterinfo, monthinfo, dayinfo)
SELECT
    w1.session_id,
    w1.sid,
    UNIX_TIMESTAMP(w1.create_time, 'yyyy-MM-dd HH:mm:ss') AS create_time,
    w1.seo_source,
    w1.ip,
    w1.area,
    CAST(IF (w1.msg_count IS NULL, 0, w1.msg_count) AS INT) AS msg_count,
    w1.origin_channel,
    w2.referrer,
    w2.from_url,
    w2.landing_page_url,
    w2.url_title,
    w2.platform_description,
    w2.other_params,
    w2.history,
    SUBSTRING(w1.create_time, 12, 2) AS hourinfo,
    SUBSTRING(w1.create_time, 1, 4) AS yearinfo,
    QUARTER(w1.create_time) AS quarterinfo,
    SUBSTRING(w1.create_time, 6, 2) AS monthinfo,
    SUBSTRING(w1.create_time, 9, 2) AS dayinfo
FROM (SELECT * FROM itcast_ods.web_chat_ems WHERE start_time='${DATESTR}') AS w1 INNER JOIN itcast_ods.web_chat_text_ems AS w2
ON w2.start_time='${DATESTR}' AND w1.id = w2.id;"